Hello all,<o:p></o:p>

We have an issue in our production environment where the activation procedure (internal activation) is not firing in the target Queue. We have been using Service Broker for a few years and have only seen this occur once and that was in a non-production environment. We ended up restarting SQL to "fix" the issue.<o:p></o:p>

The queues are enabled, activation is enabled, and all looks well. I can even call the activation proc directly in SSMS and it processes the messages in the queue. When I try to disable activation, the ALTER QUEUE statement is blocked by system SPID 26 (Status = BACKGROUND, Command = EXECUTE, Last Batch = 06/02). Does anyone have any idea of what is going on or what I can try before restarting SQL?<o:p></o:p>

Thanks,<o:p></o:p>

Thomas

<o:p></o:p>

Windows 2008 R2 Enterprise SP1 x64<o:p></o:p>

SQL Server 2008 Enterprise SP2 (10.0.4064.0 (X64))<o:p></o:p>

5 node cluster<o:p></o:p>

36 GB RAM

anyone have any ideas?

What is the MAX_QUEUE_READERS queue specification?  Try querying the sys.dm_broker_queue_monitors and sys.dm_broker_activated_tasks DMVs to check the queue montior status.

Thanks for the reply.  The MAX_QUEUE_READERS value is set to 1.  sys.dm_broker_queue_monitors

 shows one record (for the database and queue in question) with a state of RECEIVES_OCCURRING and the last_activated_time back on the 26^th of June.  sys.dm_broker_activated_tasks shows the activation stored procedure with a SPID of 26.

Does anyone know of a way to kill the activated procedure call?  Im guessing it will not allow me to just KILL the system SPID of 26. 

You can kill the the system SPID to stop the activation procedure. However, You can also try to run the ALTER QUEUE.... statement WITH  ROLLBACK IMMEDIATE. This may be a feasible alternative to killing he blocking SPID , but be warned that this will rollback all transactions that are currently open in your database.

Hope this helps.

We have a maintenance window scheduled for this weekend.  Ill try the ROLLBACK IMMEDIATE first and if that does not work Ill try to KILL the spid (I did not realize you could use the ROLLBACK IMMEDIATE on ALTERs other than ALTER DATABASE, good to know).  If all else fails, I will take the SQL Server cluster resource offline.

Thanks

• Edited by RV10Flyer Friday, July 06, 2012 1:45 PM spelling

Hi,

It is important to reveiw the sys.dm_broker_queue_monitors and the state for the queue. When a message arrives in the queue it will "NOTIFY" and change the state to NOTIFIED. When a receive statement is executed against the queue the state will change to "RECEIVES_OCCURING". Where I have seen activation not occur with new messages in the queue or "hang" is when the activation stored procedure executes but due to some code logic or error exits without doing a receive command. Adding extra error handling especially for deadlocks and profiler were useful in tracking down why the activation procedure was exiting with doing a receive.

If this occurs you can do a manual recieve from the queue to change the state and get activation to occur again.

Not sure if this is your issue, but thought I would post something.

HTH,

Bill  -- Microsoft CTS

• Proposed as answer by Sanil Mhatre Friday, July 13, 2012 12:27 PM

SPID 26 (being < 50) looks like a system SPID, but is not. Actually from 2008 onwards System SPIDs are not guarented to be in 1 = 50 range